﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class APVoucherManager : ConnectionManager
    {
        #region Add/Update
        public SqlException AddVoucher(APVoucherUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT OAPV (DocNum, CardCode, CardName, PostingDate, Remarks, Reference, TotalAmount, CreatedBy, DateCreated, ModifiedBy, DateModified, DocStatus) VALUES (@DocNum, @CardCode, @CardName, @PostingDate, @Remarks, @Reference, @TotalAmount, @CreatedBy, @DateCreated, @ModifiedBy, @DateModified, @DocStatus)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@TotalAmount", oUnit.TotalAmount));



                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocStatus));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));

                oCommand.ExecuteNonQuery();


                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO APV1 (DocNum, DocType, RefDocument, RefInvoice, RefDocDate, CardCode, CardName, Amount) VALUES (@DocNum, @DocType, @RefDocument, @RefInvoice, @RefDocDate, @CardCode, @CardName, @Amount)";

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@RefDocument", oRow["RefDocument"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@RefInvoice", oRow["RefInvoice"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@RefDocDate", Convert.ToDateTime(oRow["RefDocDate"].ToString())));

                    oCommand.Parameters.Add(new SqlParameter("@CardCode", Convert.ToString(oRow["CardCode"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CardName", Convert.ToString(oRow["CardName"].ToString())));

                    oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));


                    oCommand.ExecuteNonQuery();

                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateVoucher(APVoucherUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE OAPV SET CardCode=@CardCode, CardName=@CardName, PostingDate=@PostingDate, Remarks=@Remarks, Reference=@Reference, TotalAmount=@TotalAmount, ModifiedBy=@ModifiedBy, DateModified=@DateModified, DocStatus=@DocStatus WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@TotalAmount", oUnit.TotalAmount));

                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocStatus));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE FROM APV1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO APV1 (DocNum, DocType, RefDocument, RefInvoice, RefDocDate, CardCode, CardName, Amount) VALUES (@DocNum, @DocType, @RefDocument, @RefInvoice, @RefDocDate, @CardCode, @CardName, @Amount)";

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@DocType", oRow["DocType"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefDocument", oRow["RefDocument"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefInvoice", oRow["RefInvoice"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefDocDate", Convert.ToDateTime(oRow["RefDocDate"].ToString())));

                        oCommand.Parameters.Add(new SqlParameter("@CardCode", Convert.ToString(oRow["CardCode"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CardName", Convert.ToString(oRow["CardName"].ToString())));

                        oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"])));


                        oCommand.ExecuteNonQuery();
                    }

                }
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException SetAppliedAmount(string DocumentNo, string _AMOUNT)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OAPV SET AppliedAmount=AppliedAmount+@AppliedAmount WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNo));
                oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", _AMOUNT));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Updating Status

        public SqlException CloseDocument(string _DOC_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE OAPV SET DocStatus='Closed' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }


        public SqlException CancelDocument(string _DOC_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OAPV SET DocStatus='Canceled' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }


        public SqlException PostDocument(string _DOC_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OAPV SET DocStatus='Posted' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        #endregion

        #region Retrieving Data

        public DataTable GetAPVoucher(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OAPV WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAPVoucherByDocNum(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _DOC_NUMBER = "%" + _DOC_NUMBER + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OAPV WHERE DocNum Like @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAPVoucherByVendor(string _VENDOR_NAME)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _VENDOR_NAME = "%" + _VENDOR_NAME + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OAPV WHERE CardName Like @CardName";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _VENDOR_NAME));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetPostedAPVoucherByDocNum(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _DOC_NUMBER = "%" + _DOC_NUMBER + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OAPV WHERE DocNum Like @DocNum AND DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetPostedAPVoucherByVendor(string _VENDOR_NAME)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _VENDOR_NAME = "%" + _VENDOR_NAME + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OAPV WHERE CardName Like @CardName AND DocStatus='Posted'";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _VENDOR_NAME));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetAPVLineItems(string _DOC_NUMBER)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM APV1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOC_NUMBER));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        #endregion
    }
}
